--BookIt projekt adatbázisa
--néhány mintaadattal együtt
--megjegyzés:
--oracleben nincs AUTO_INCREMENT, így ezeket számlálókkal
--és triggerekkel valósítottam meg

DROP TABLE FELHASZNALOK CASCADE CONSTRAINTS;                   
DROP TABLE ARUHAZAK CASCADE CONSTRAINTS;                       
DROP TABLE KIADOK CASCADE CONSTRAINTS;                         
DROP TABLE SZERZOK CASCADE CONSTRAINTS;                        
DROP TABLE TERMEKEK CASCADE CONSTRAINTS;                       
DROP TABLE NYILVANTARTAS CASCADE CONSTRAINTS;                  
DROP TABLE MUFAJOK CASCADE CONSTRAINTS;                        
DROP TABLE SZAMLAK CASCADE CONSTRAINTS;                        
DROP TABLE TERMEKMUFAJ CASCADE CONSTRAINTS;                    
DROP TABLE SZAMLATETEL CASCADE CONSTRAINTS;                    
DROP TABLE KONYVEK CASCADE CONSTRAINTS;                        
DROP TABLE EKONYVEK CASCADE CONSTRAINTS;                       
DROP TABLE FILMEK CASCADE CONSTRAINTS;                         
DROP TABLE ZENEK CASCADE CONSTRAINTS;                          
DROP TABLE KEDVEZMENYEK CASCADE CONSTRAINTS;                   
DROP TABLE KOSAR CASCADE CONSTRAINTS; 
DROP SEQUENCE FELHASZNALOK_AI_SEQ;
DROP SEQUENCE ARUHAZAK_AI_SEQ;
DROP SEQUENCE TERMEKEK_AI_SEQ;
DROP SEQUENCE SZERZOK_AI_SEQ;
DROP SEQUENCE KIADOK_AI_SEQ;
DROP SEQUENCE MUFAJOK_AI_SEQ;
DROP SEQUENCE SZAMLAK_AI_SEQ;
DROP SEQUENCE KEDVEZMENYEK_AI_SEQ;

--Felhasználók tábla
--megjegyzés: jelszo->sha1 titkosítású string lesz, ami 40 karakter
CREATE TABLE Felhasznalok(
	felhasznaloi_azonosito INTEGER PRIMARY KEY,
	felhasznalonev VARCHAR2(25),
	jelszo VARCHAR2(40),
	email VARCHAR2(30),
	valodi_nev VARCHAR2(40),
	lakcim VARCHAR2(40),
	telefonszam VARCHAR2(20),
	jogosultsag INTEGER
);
--Számláló létrehozása auto_incrementhez
CREATE SEQUENCE felhasznalok_ai_seq
	START WITH 1
	INCREMENT BY 1
	NOMAXVALUE;
--Trigger létrehozása auto_incrementhez
CREATE OR REPLACE TRIGGER felhasznalok_ai_trigger
	BEFORE INSERT
	ON Felhasznalok
	FOR EACH ROW
BEGIN
	SELECT felhasznalok_ai_seq.nextval
	INTO :NEW.felhasznaloi_azonosito
	FROM dual;
END;
/
--Áruházak tábla
CREATE TABLE Aruhazak(
	aruhaz_azonosito INTEGER PRIMARY KEY,
	cim VARCHAR2(200),
	telefonszam VARCHAR2(40)
);
--Számláló létrehozása auto_incrementhez
CREATE SEQUENCE aruhazak_ai_seq
	START WITH 1
	INCREMENT BY 1
	NOMAXVALUE;
--Trigger létrehozása auto_incrementhez
CREATE OR REPLACE TRIGGER aruhazak_ai_trigger
	BEFORE INSERT
	ON Aruhazak
	FOR EACH ROW
BEGIN
	SELECT aruhazak_ai_seq.nextval
	INTO :NEW.aruhaz_azonosito
	FROM dual;
END;
/
--Szerzõk tábla
CREATE TABLE Szerzok(
	szerzo_azonosito INTEGER PRIMARY KEY,
	szerzo_neve VARCHAR2(30)
);
--Számláló létrehozása auto_incrementhez
CREATE SEQUENCE szerzok_ai_seq
	START WITH 1
	INCREMENT BY 1
	NOMAXVALUE;
--Trigger létrehozása auto_incrementhez
CREATE OR REPLACE TRIGGER szerzok_ai_trigger
	BEFORE INSERT
	ON Szerzok
	FOR EACH ROW
BEGIN
	SELECT szerzok_ai_seq.nextval
	INTO :NEW.szerzo_azonosito
	FROM dual;
END;
/
--Kiadók tábla
CREATE TABLE Kiadok(
	kiado_azonosito INTEGER PRIMARY KEY,
	kiado_neve VARCHAR2(30),
	kiado_cime VARCHAR2(40)
);
--Számláló létrehozása auto_incrementhez
CREATE SEQUENCE kiadok_ai_seq
	START WITH 1
	INCREMENT BY 1
	NOMAXVALUE;
--Trigger létrehozása auto_incrementhez
CREATE OR REPLACE TRIGGER kiadok_ai_trigger
	BEFORE INSERT
	ON Kiadok
	FOR EACH ROW
BEGIN
	SELECT kiadok_ai_seq.nextval
	INTO :NEW.kiado_azonosito
	FROM dual;
END;
/
--Termékek tábla
CREATE TABLE Termekek(
	termek_azonosito INTEGER PRIMARY KEY,
	szerzo_azonosito INTEGER,
	kiado_azonosito INTEGER,
	kiadas_eve INTEGER,
	cim VARCHAR2(100),
	leiras VARCHAR2(2000),
	ar INTEGER,
	FOREIGN KEY (szerzo_azonosito) REFERENCES Szerzok(szerzo_azonosito),
	FOREIGN KEY (kiado_azonosito) REFERENCES Kiadok(kiado_azonosito)
);
--Számláló létrehozása auto_incrementhez
CREATE SEQUENCE termekek_ai_seq
	START WITH 1
	INCREMENT BY 1
	NOMAXVALUE;
--Trigger létrehozása auto_incrementhez
CREATE OR REPLACE TRIGGER termekek_ai_trigger
	BEFORE INSERT
	ON Termekek
	FOR EACH ROW
BEGIN
	SELECT termekek_ai_seq.nextval
	INTO :NEW.termek_azonosito
	FROM dual;
END;
/
--Nyilvántartás tábla
CREATE TABLE Nyilvantartas(
	aruhaz_azonosito INTEGER,
	termek_azonosito INTEGER,
	db INTEGER,
	PRIMARY KEY ( aruhaz_azonosito, termek_azonosito ),
	FOREIGN KEY (termek_azonosito) REFERENCES Termekek(termek_azonosito),
	FOREIGN KEY (aruhaz_azonosito) REFERENCES Aruhazak(aruhaz_azonosito)
);

--Mûfajok tábla
CREATE TABLE Mufajok(
	mufaj_azonosito INTEGER PRIMARY KEY,
	mufaj_neve VARCHAR2(30),
	szulo_mufaj_azonosito INTEGER,
	melysegi_szint INTEGER
);
--Számláló létrehozása auto_incrementhez
CREATE SEQUENCE mufajok_ai_seq
	START WITH 1
	INCREMENT BY 1
	NOMAXVALUE;
--Trigger létrehozása auto_incrementhez
CREATE OR REPLACE TRIGGER mufajok_ai_trigger
	BEFORE INSERT
	ON Mufajok
	FOR EACH ROW
BEGIN
	SELECT mufajok_ai_seq.nextval
	INTO :NEW.mufaj_azonosito
	FROM dual;
END;
/
--TermékMûfaj tábla
CREATE TABLE TermekMufaj(
	termek_azonosito INTEGER,
	mufaj_azonosito INTEGER,
	PRIMARY KEY (termek_azonosito, mufaj_azonosito),
	FOREIGN KEY (termek_azonosito) REFERENCES Termekek(termek_azonosito),
	FOREIGN KEY (mufaj_azonosito) REFERENCES Mufajok(mufaj_azonosito)
);

--Számlák tábla
CREATE TABLE Szamlak(
	szamla_azonosito INTEGER PRIMARY KEY,
	felhasznaloi_azonosito INTEGER,
	aruhaz_azonosito INTEGER,
	atvetel_helye INTEGER,
	datum DATE,
	FOREIGN KEY (felhasznaloi_azonosito) REFERENCES Felhasznalok(felhasznaloi_azonosito),
	FOREIGN KEY (aruhaz_azonosito) REFERENCES Aruhazak(aruhaz_azonosito),
	FOREIGN KEY (atvetel_helye) REFERENCES Aruhazak(aruhaz_azonosito)
);
--Számláló létrehozása auto_incrementhez
CREATE SEQUENCE szamlak_ai_seq
	START WITH 1
	INCREMENT BY 1
	NOMAXVALUE;
--Trigger létrehozása auto_incrementhez
CREATE OR REPLACE TRIGGER szamlak_ai_trigger
	BEFORE INSERT
	ON Szamlak
	FOR EACH ROW
BEGIN
	SELECT szamlak_ai_seq.nextval
	INTO :NEW.szamla_azonosito
	FROM dual;
END;
/
--SzámlaTételek tábla
CREATE TABLE SzamlaTetel(
	szamla_azonosito INTEGER,
	termek_azonosito INTEGER,
	db INTEGER,
	PRIMARY KEY ( szamla_azonosito,termek_azonosito ),
	FOREIGN KEY (szamla_azonosito) REFERENCES Szamlak(szamla_azonosito),
	FOREIGN KEY (termek_azonosito) REFERENCES Termekek(termek_azonosito)
);

--Könyvek tábla
CREATE TABLE Konyvek(
	termek_azonosito INTEGER PRIMARY KEY,
	meret VARCHAR2(30),
	oldalszam INTEGER,
	kotes VARCHAR2(20),
	FOREIGN KEY (termek_azonosito) REFERENCES Termekek(termek_azonosito)
);

--E-Könyvek tábla
CREATE TABLE Ekonyvek(
	termek_azonosito INTEGER PRIMARY KEY,
	meret INTEGER,
	oldalszam INTEGER,
	kiterjesztes VARCHAR2(20),
	FOREIGN KEY (termek_azonosito) REFERENCES Termekek(termek_azonosito)
);

--Filmek tábla
CREATE TABLE Filmek(
	termek_azonosito INTEGER PRIMARY KEY,
	idotartam INTEGER,
	FOREIGN KEY (termek_azonosito) REFERENCES Termekek(termek_azonosito)
);

--Zenék tábla
CREATE TABLE Zenek(
	termek_azonosito INTEGER PRIMARY KEY,
	idotartam INTEGER,
	FOREIGN KEY (termek_azonosito) REFERENCES Termekek(termek_azonosito)
);

--Kedvezmények tábla
CREATE TABLE Kedvezmenyek(
	kedvezmeny_azonosito INTEGER PRIMARY KEY,
	termek_azonosito INTEGER,
	aruhaz_azonosito INTEGER,
	akcio_szazalek INTEGER,
	akcio_szazalek_torzs INTEGER,
	osszevonhato INTEGER,
	db_korlat INTEGER,
	akcio_kezdete DATE,
	akcio_vege DATE,
	FOREIGN KEY (termek_azonosito) REFERENCES Termekek(termek_azonosito),
	FOREIGN KEY (aruhaz_azonosito) REFERENCES Aruhazak(aruhaz_azonosito)
);
--Számláló létrehozása auto_incrementhez
CREATE SEQUENCE kedvezmenyek_ai_seq
	START WITH 1
	INCREMENT BY 1
	NOMAXVALUE;
--Trigger létrehozása auto_incrementhez
CREATE OR REPLACE TRIGGER kedvezmenyek_ai_trigger
	BEFORE INSERT
	ON Kedvezmenyek
	FOR EACH ROW
BEGIN
	SELECT kedvezmenyek_ai_seq.nextval
	INTO :NEW.kedvezmeny_azonosito
	FROM dual;
END;
/

--Kosár tárbla
CREATE TABLE Kosar(
	felhasznaloi_azonosito INTEGER PRIMARY KEY,
	termek_azonosito INTEGER,
	aruhaz_azonosito INTEGER,
	db INTEGER,
	FOREIGN KEY (felhasznaloi_azonosito) REFERENCES Felhasznalok(felhasznaloi_azonosito),
	FOREIGN KEY (termek_azonosito) REFERENCES Termekek(termek_azonosito),
	FOREIGN KEY (aruhaz_azonosito) REFERENCES Aruhazak(aruhaz_azonosito)
);

--archív számlák
create table SzamlakArchiv(
  szamla_azonosito INTEGER PRIMARY KEY,
  vasarlo VARCHAR2(40),
  vasarlasi_cim VARCHAR2(200),
  atveteli_cim VARCHAR2(200),
  datum DATE
);
--archív számlákon szereplő tételek
create table szamlatetelarchiv(
  szamla_azonosito INTEGER,
  termek_neve VARCHAR(100),
  db INTEGER,
  ar INTEGER
);

----------------------------Tárolt eljárások, triggerek stb----------------------------

--kurzor a visszaadáshoz
create or replace
PACKAGE MYPACKAGE AS 
  TYPE kurzor IS REF CURSOR;
END MYPACKAGE;
/
--tárolt eljárás alműfajok lekérdezéséhez adott szulőhöz tartozóan
create or replace
FUNCTION GETALLMUFAJBYSZULO( tabla IN varchar2, szulo IN mufajok.szulo_mufaj_azonosito%TYPE )  RETURN MYPACKAGE.kurzor
AS 
  kurzor MYPACKAGE.kurzor;
  sql_query varchar2(1000);
BEGIN
  sql_query := 'SELECT * FROM mufajok WHERE mufajok.szulo_mufaj_azonosito = ' || szulo || ' AND mufajok.mufaj_azonosito IN (
              SELECT mufaj_azonosito
              FROM TermekMufaj
              WHERE termek_azonosito IN (
                SELECT termek_azonosito
                FROM '|| tabla ||'
              )
             )';
  OPEN kurzor FOR sql_query;
  RETURN kurzor;
END GETALLMUFAJBYSZULO;
/
---mélység automatikus meghatározása
create or replace
TRIGGER MUFAJOK_AM_TRIGGER
	BEFORE INSERT
	ON MUFAJOK
	FOR EACH ROW
DECLARE
  mely MUFAJOK.melysegi_szint%TYPE;  
BEGIN
  IF :NEW.szulo_mufaj_azonosito = 0 THEN :NEW.melysegi_szint := 0;
  ELSE
    SELECT melysegi_szint
    INTO mely
    FROM MUFAJOK
    WHERE mufaj_azonosito = :NEW.szulo_mufaj_azonosito;
    :NEW.melysegi_szint := (mely + 1);
  END IF;
END;
/
---összes termék lekérdezése az adott kategóriában
CREATE OR REPLACE FUNCTION GETALLTERMEKBYCATEGORY( tabla IN varchar2, az IN mufajok.mufaj_azonosito%TYPE ) RETURN MYPACKAGE.kurzor
AS 
  kurzor MYPACKAGE.kurzor;
  sql_query varchar2(1000);
BEGIN
  sql_query := '
  SELECT Termekek.*, '|| tabla ||'.*, szerzok.szerzo_neve, kiadok.kiado_neve, kiadok.kiado_cime
  FROM '|| tabla ||'
  JOIN Termekek ON '|| tabla ||'.termek_azonosito = termekek.termek_azonosito
  JOIN Szerzok ON termekek.szerzo_azonosito = szerzok.szerzo_azonosito
  JOIN Kiadok ON termekek.kiado_azonosito = kiadok.kiado_azonosito';
  
  IF (az > 0) THEN
    sql_query := sql_query || ' JOIN TermekMufaj ON '|| tabla ||'.termek_azonosito = TermekMufaj.termek_azonosito
    WHERE termekmufaj.mufaj_azonosito = ' || az;
  END IF;
  
  OPEN kurzor FOR sql_query;
  RETURN kurzor;
END GETALLTERMEKBYCATEGORY;
/
--összes termék lekérdezése adott számláról (ha számlaaz=0 akkor az össszes számlát figyelembe veszi)
create or replace
FUNCTION GETALLTETELBYSZAMLAAZ( tabla IN varchar2, az IN szamlak.szamla_azonosito%TYPE ) RETURN MYPACKAGE.kurzor
AS 
  kurzor MYPACKAGE.kurzor;
  sql_query varchar2(1000);
BEGIN
  sql_query := '
    SELECT SzamlaTetel.*,Termekek.*, '|| tabla ||'.*, szerzok.szerzo_neve, kiadok.kiado_neve, kiadok.kiado_cime
    FROM '|| tabla ||'
    JOIN Termekek ON '|| tabla ||'.termek_azonosito = termekek.termek_azonosito
    JOIN szamlatetel ON '|| tabla ||'.termek_azonosito = szamlatetel.termek_azonosito
    JOIN Szerzok ON termekek.szerzo_azonosito = szerzok.szerzo_azonosito
    JOIN Kiadok ON termekek.kiado_azonosito = kiadok.kiado_azonosito';
    IF (az > 0) THEN
      sql_query := sql_query || ' WHERE szamlatetel.szamla_azonosito = ' || az;
    END IF;
    sql_query := sql_query || ' ORDER BY szamlatetel.szamla_azonosito';
  
  OPEN kurzor FOR sql_query;
  RETURN kurzor;
END GETALLTETELBYSZAMLAAZ;
/
--automata árukészlet beállítás
CREATE OR REPLACE TRIGGER TERMEKEK_AK_TRIGGER 
AFTER INSERT ON TERMEKEK 
FOR EACH ROW
BEGIN
	FOR num IN (SELECT aruhaz_azonosito FROM aruhazak) LOOP
	  INSERT INTO nyilvantartas (aruhaz_azonosito,termek_azonosito,db) VALUES( num.aruhaz_azonosito,:NEW.termek_azonosito,10 );
	END LOOP;
END;
/
--ha adott műfajba betesznek egy terméket akkor automatikusan bekerül annak szülőműfajaiba is
create or replace
TRIGGER FOMUFAJOKBAIS 
BEFORE INSERT ON TERMEKMUFAJ 
FOR EACH ROW 
DECLARE
  v_szulo mufajok.mufaj_azonosito%TYPE;
  v_db INTEGER;
BEGIN
  SELECT count(*) INTO v_db FROM TERMEKMUFAJ WHERE mufaj_azonosito = :NEW.mufaj_azonosito AND termek_azonosito = :NEW.termek_azonosito;
  IF v_db > 0 THEN
     raise_application_error(-20000,'Ne szurd be!');
  END IF;
  SELECT szulo_mufaj_azonosito INTO v_szulo FROM MUFAJOK WHERE mufaj_azonosito = :NEW.mufaj_azonosito;
  IF v_szulo > 0 THEN
	SELECT count(*) INTO v_db FROM TERMEKMUFAJ WHERE mufaj_azonosito = v_szulo AND termek_azonosito = :NEW.termek_azonosito;
	IF v_db = 0 THEN --nincs még bent a megadott mufaj szülőjében
		INSERT INTO TERMEKMUFAJ (termek_azonosito, mufaj_azonosito) VALUES (:NEW.termek_azonosito,v_szulo); 
	END IF;
  END IF;
EXCEPTION
  WHEN OTHERS THEN NULL;
END;
/
create or replace
FUNCTION GETSHOPSBYPRODUCTIDS( ids IN varchar2 ) RETURN MYPACKAGE.kurzor
AS 
  kurzor MYPACKAGE.kurzor;
  sql_query varchar2(1000);
BEGIN
  sql_query := '
  SELECT nyilvantartas.termek_azonosito, nyilvantartas.aruhaz_azonosito, aruhazak.cim, aruhazak.telefonszam, nyilvantartas.db
  FROM Nyilvantartas JOIN Aruhazak ON nyilvantartas.aruhaz_azonosito = aruhazak.aruhaz_azonosito
  WHERE nyilvantartas.termek_azonosito IN(' || ids || ')';
  
  OPEN kurzor FOR sql_query;
  RETURN kurzor;
END GETSHOPSBYPRODUCTIDS;
/
create or replace
FUNCTION GETCATEGORIESBYPRODUCTIDS( ids IN varchar2 ) RETURN MYPACKAGE.kurzor
AS 
  kurzor MYPACKAGE.kurzor;
  sql_query varchar2(1000);
BEGIN
  sql_query := '
  SELECT termekmufaj.termek_azonosito, termekmufaj.mufaj_azonosito, mufajok.mufaj_neve, mufajok.szulo_mufaj_azonosito, mufajok.melysegi_szint
  FROM termekmufaj JOIN mufajok ON termekmufaj.mufaj_azonosito = mufajok.mufaj_azonosito
  WHERE termekmufaj.termek_azonosito IN(' || ids || ') ORDER BY termekmufaj.termek_azonosito, termekmufaj.mufaj_azonosito';
    
  OPEN kurzor FOR sql_query;
  RETURN kurzor;
END GETCATEGORIESBYPRODUCTIDS;
/
create or replace
FUNCTION GETPRODUCTSBYPRODUCTIDS( tabla IN varchar2, ids IN varchar2 ) RETURN MYPACKAGE.kurzor
AS 
  kurzor MYPACKAGE.kurzor;
  sql_query varchar2(1000);
BEGIN
  sql_query := '
  SELECT Termekek.*, '|| tabla ||'.*, szerzok.szerzo_neve, kiadok.kiado_neve, kiadok.kiado_cime
  FROM '|| tabla ||'
  JOIN Termekek ON '|| tabla ||'.termek_azonosito = termekek.termek_azonosito
  JOIN Szerzok ON termekek.szerzo_azonosito = szerzok.szerzo_azonosito
  JOIN Kiadok ON termekek.kiado_azonosito = kiadok.kiado_azonosito
  WHERE termekek.termek_azonosito IN( ' || ids || ' )';
    
  OPEN kurzor FOR sql_query;
  RETURN kurzor;
END GETPRODUCTSBYPRODUCTIDS;
/
create or replace
FUNCTION GETWEEKLYTOP10( tabla IN varchar2 ) RETURN MYPACKAGE.kurzor
AS 
  kurzor MYPACKAGE.kurzor;
  sql_query varchar2(1000);
BEGIN
IF NVL(tabla,'-null-') != '-null-' THEN
  IF tabla = 'Konyvek' THEN
    OPEN kurzor FOR
    SELECT *
    FROM (
      SELECT termek_azonosito
      FROM szamlatetel
      WHERE szamlatetel.szamla_azonosito in (
        SELECT szamla_azonosito
        FROM szamlak
        WHERE (datum >= to_date(sysdate-to_char( sysdate,'D')-6,'YY-MON-DD')) AND (datum <= to_date(sysdate-to_char( sysdate,'D'),'YY-MON-DD'))
      ) AND termek_azonosito IN ( 
        SELECT termek_azonosito
        FROM Konyvek
      )
      GROUP BY termek_azonosito
      ORDER BY count(*) DESC
    )
    WHERE ROWNUM <= 10;
  ELSIF tabla = 'Ekonyvek' THEN
    OPEN kurzor FOR
    SELECT *
    FROM (
      SELECT termek_azonosito
      FROM szamlatetel
      WHERE szamlatetel.szamla_azonosito in (
        SELECT szamla_azonosito
        FROM szamlak
        WHERE (datum >= to_date(sysdate-to_char( sysdate,'D')-6,'YY-MON-DD')) AND (datum <= to_date(sysdate-to_char( sysdate,'D'),'YY-MON-DD'))
      ) AND termek_azonosito IN ( 
        SELECT termek_azonosito
        FROM Ekonyvek
      )
      GROUP BY termek_azonosito
      ORDER BY count(*) DESC
    )
    WHERE ROWNUM <= 10;
  ELSIF tabla = 'Filmek' THEN
    OPEN kurzor FOR
    SELECT *
    FROM (
      SELECT termek_azonosito
      FROM szamlatetel
      WHERE szamlatetel.szamla_azonosito in (
        SELECT szamla_azonosito
        FROM szamlak
        WHERE (datum >= to_date(sysdate-to_char( sysdate,'D')-6,'YY-MON-DD')) AND (datum <= to_date(sysdate-to_char( sysdate,'D'),'YY-MON-DD'))
      ) AND termek_azonosito IN ( 
        SELECT termek_azonosito
        FROM Filmek
      )
      GROUP BY termek_azonosito
      ORDER BY count(*) DESC
    )
    WHERE ROWNUM <= 10;
  ELSIF tabla = 'Zenek' THEN
    OPEN kurzor FOR
    SELECT *
    FROM (
      SELECT termek_azonosito
      FROM szamlatetel
      WHERE szamlatetel.szamla_azonosito in (
        SELECT szamla_azonosito
        FROM szamlak
        WHERE (datum >= to_date(sysdate-to_char( sysdate,'D')-6,'YY-MON-DD')) AND (datum <= to_date(sysdate-to_char( sysdate,'D'),'YY-MON-DD'))
      ) AND termek_azonosito IN ( 
        SELECT termek_azonosito
        FROM Zenek
      )
      GROUP BY termek_azonosito
      ORDER BY count(*) DESC
    )
    WHERE ROWNUM <= 10;
  END IF;
ELSE
  OPEN kurzor FOR
  SELECT *
  FROM (
    SELECT termek_azonosito
    FROM szamlatetel
    WHERE szamlatetel.szamla_azonosito in (
      SELECT szamla_azonosito
      FROM szamlak
      WHERE (datum >= to_date(sysdate-to_char( sysdate,'D')-6,'YY-MON-DD')) AND (datum <= to_date(sysdate-to_char( sysdate,'D'),'YY-MON-DD'))
    )
    GROUP BY termek_azonosito
    ORDER BY count(*) DESC
  )
  WHERE ROWNUM <= 10;
END IF;
 RETURN kurzor;
END GETWEEKLYTOP10;
/
create or replace
FUNCTION GETMONTHLYTOP10( tabla IN varchar2 ) RETURN MYPACKAGE.kurzor
AS 
  kurzor MYPACKAGE.kurzor;
  sql_query varchar2(1000);
BEGIN
IF NVL(tabla,'-null-') != '-null-' THEN
  IF tabla = 'Konyvek' THEN
    OPEN kurzor FOR
    SELECT *
    FROM (
      SELECT termek_azonosito
      FROM szamlatetel
      WHERE szamlatetel.szamla_azonosito in (
        SELECT szamla_azonosito
        FROM szamlak
        WHERE (datum >= to_date(trunc(trunc(sysdate, 'MM') - 1, 'MM'),'YY-MON-DD')) AND (datum <= to_date(trunc(sysdate, 'MM') - 1,'YY-MON-DD'))
      ) AND termek_azonosito IN ( 
        SELECT termek_azonosito
        FROM Konyvek
      )
      GROUP BY termek_azonosito
      ORDER BY count(*) DESC
    )
    WHERE ROWNUM <= 10;
  ELSIF tabla = 'Ekonyvek' THEN
    OPEN kurzor FOR
    SELECT *
    FROM (
      SELECT termek_azonosito
      FROM szamlatetel
      WHERE szamlatetel.szamla_azonosito in (
        SELECT szamla_azonosito
        FROM szamlak
        WHERE (datum >= to_date(trunc(trunc(sysdate, 'MM') - 1, 'MM'),'YY-MON-DD')) AND (datum <= to_date(trunc(sysdate, 'MM') - 1,'YY-MON-DD'))
      ) AND termek_azonosito IN ( 
        SELECT termek_azonosito
        FROM Ekonyvek
      )
      GROUP BY termek_azonosito
      ORDER BY count(*) DESC
    )
    WHERE ROWNUM <= 10;
  ELSIF tabla = 'Filmek' THEN
    OPEN kurzor FOR
    SELECT *
    FROM (
      SELECT termek_azonosito
      FROM szamlatetel
      WHERE szamlatetel.szamla_azonosito in (
        SELECT szamla_azonosito
        FROM szamlak
        WHERE (datum >= to_date(trunc(trunc(sysdate, 'MM') - 1, 'MM'),'YY-MON-DD')) AND (datum <= to_date(trunc(sysdate, 'MM') - 1,'YY-MON-DD'))
      ) AND termek_azonosito IN ( 
        SELECT termek_azonosito
        FROM Filmek
      )
      GROUP BY termek_azonosito
      ORDER BY count(*) DESC
    )
    WHERE ROWNUM <= 10;
  ELSIF tabla = 'Zenek' THEN
    OPEN kurzor FOR
    SELECT *
    FROM (
      SELECT termek_azonosito
      FROM szamlatetel
      WHERE szamlatetel.szamla_azonosito in (
        SELECT szamla_azonosito
        FROM szamlak
        WHERE (datum >= to_date(trunc(trunc(sysdate, 'MM') - 1, 'MM'),'YY-MON-DD')) AND (datum <= to_date(trunc(sysdate, 'MM') - 1,'YY-MON-DD'))
      ) AND termek_azonosito IN ( 
        SELECT termek_azonosito
        FROM Zenek
      )
      GROUP BY termek_azonosito
      ORDER BY count(*) DESC
    )
    WHERE ROWNUM <= 10;
  END IF;
ELSE
  OPEN kurzor FOR
  SELECT *
  FROM (
    SELECT termek_azonosito
    FROM szamlatetel
    WHERE szamlatetel.szamla_azonosito in (
      SELECT szamla_azonosito
      FROM szamlak
      WHERE (datum >= to_date(trunc(trunc(sysdate, 'MM') - 1, 'MM'),'YY-MON-DD')) AND (datum <= to_date(trunc(sysdate, 'MM') - 1,'YY-MON-DD'))
    )
    GROUP BY termek_azonosito
    ORDER BY count(*) DESC
  )
  WHERE ROWNUM <= 10;
END IF;
  RETURN kurzor;
END GETMONTHLYTOP10;
/

create or replace
FUNCTION KERES( 
tabla IN varchar2, 
cim IN termekek.cim%TYPE, 
szerzo IN szerzok.szerzo_azonosito%TYPE, 
kiado IN kiadok.kiado_azonosito%TYPE, 
ev IN termekek.kiadas_eve%TYPE, 
mufaj IN termekmufaj.mufaj_azonosito%TYPE, 
ar_tol IN termekek.ar%TYPE,
ar_ig IN termekek.ar%TYPE,
casesensitive IN INTEGER,
pontos IN INTEGER ) RETURN MYPACKAGE.kurzor
AS 
  kurzor MYPACKAGE.kurzor;
  sql_query varchar2(1000);
  where_feltetel varchar2(1000) := '';
BEGIN
---milyen termékre keresünk?
IF tabla = 'Konyvek' THEN
  sql_query := '
  SELECT distinct termekek.termek_azonosito, termekek.szerzo_azonosito, termekek.kiado_azonosito, termekek.kiadas_eve, termekek.cim, termekek.leiras, termekek.ar, konyvek.kotes, konyvek.meret, konyvek.oldalszam, szerzok.szerzo_neve, kiadok.kiado_neve, kiadok.kiado_cime
  FROM Konyvek
  JOIN Termekek ON konyvek.termek_azonosito = termekek.termek_azonosito';
ELSIF tabla = 'Ekonyvek' THEN
  sql_query := '
  SELECT distinct termekek.termek_azonosito, termekek.szerzo_azonosito, termekek.kiado_azonosito, termekek.kiadas_eve, termekek.cim, termekek.leiras, termekek.ar, ekonyvek.kiterjesztes, ekonyvek.meret, ekonyvek.oldalszam, szerzok.szerzo_neve, kiadok.kiado_neve, kiadok.kiado_cime
  FROM Ekonyvek
  JOIN Termekek ON Ekonyvek.termek_azonosito = termekek.termek_azonosito';
ELSIF tabla = 'Filmek' THEN
  sql_query := '
  SELECT distinct termekek.termek_azonosito, termekek.szerzo_azonosito, termekek.kiado_azonosito, termekek.kiadas_eve, termekek.cim, termekek.leiras, termekek.ar, filmek.idotartam, szerzok.szerzo_neve, kiadok.kiado_neve, kiadok.kiado_cime
  FROM Filmek
  JOIN Termekek ON Filmek.termek_azonosito = termekek.termek_azonosito';
ELSIF tabla = 'Zenek' THEN
  sql_query := '
  SELECT distinct termekek.termek_azonosito, termekek.szerzo_azonosito, termekek.kiado_azonosito, termekek.kiadas_eve, termekek.cim, termekek.leiras, termekek.ar, zenek.idotartam, szerzok.szerzo_neve, kiadok.kiado_neve, kiadok.kiado_cime
  FROM Zenek
  JOIN Termekek ON Zenek.termek_azonosito = termekek.termek_azonosito';
ELSE RETURN kurzor;
END IF;
                
  sql_query := sql_query || '
  JOIN Szerzok ON termekek.szerzo_azonosito = szerzok.szerzo_azonosito
  JOIN Kiadok ON termekek.kiado_azonosito = kiadok.kiado_azonosito
  JOIN TermekMufaj ON termekek.termek_azonosito = termekmufaj.termek_azonosito
  WHERE ';
  
  --címre szurunk-e?
  IF NVL( cim, '-null-' ) != '-null-' THEN
    --teljes egyezés kell?
    IF pontos = 1 THEN
      --kis-nagy betuk megkülönböztetése vagy sem
      IF casesensitive = 1 THEN
        where_feltetel := where_feltetel || 'termekek.cim = ''' || cim || '''';
      ELSE
        where_feltetel := where_feltetel || 'lower(termekek.cim) = lower( ''' || cim || ''' )';
      END IF;
    ELSE
      --kis-nagy betuk megkülönböztetése vagy sem
      IF casesensitive = 1 THEN
        where_feltetel := where_feltetel || 'termekek.cim LIKE  ''%' || cim || '%''';
      ELSE
        where_feltetel := where_feltetel || 'lower(termekek.cim) LIKE lower( ''%' || cim || '%'' )';
      END IF;
    END IF;
  END IF;

  --szerzore szurünk-e?
  IF szerzo != 0 THEN
    --ha már adtunk hozzá a where feltételhez feltételt akkor AND kell a következo feltétel elé
    IF NVL( where_feltetel, '-null-' ) != '-null-' THEN where_feltetel := where_feltetel || ' AND '; END IF;
    where_feltetel := where_feltetel || 'szerzok.szerzo_azonosito=' || szerzo;
  END IF;
  
  --kiadóra szurünk-e?
  IF kiado != 0 THEN
    IF NVL( where_feltetel, '-null-' ) != '-null-' THEN where_feltetel := where_feltetel || ' AND '; END IF;
    where_feltetel := where_feltetel || 'kiadok.kiado_azonosito=' || kiado;
  END IF;
  
  --kiadás évére szurunk-e?
  IF ev != 0 THEN
    IF NVL( where_feltetel, '-null-' ) != '-null-' THEN where_feltetel := where_feltetel || ' AND '; END IF;
    where_feltetel := where_feltetel || 'termekek.kiadas_eve=' || ev;
  END IF;
  
  --mufakra szurunk-e?
  IF mufaj != 0 THEN
    IF NVL( where_feltetel, '-null-' ) != '-null-' THEN where_feltetel := where_feltetel || ' AND '; END IF;
    where_feltetel := where_feltetel || 'termekmufaj.mufaj_azonosito=' || mufaj;
  END IF;
  
  --árra szurunk-e?
  IF ar_ig != 0 THEN
    IF NVL( where_feltetel, '-null-' ) != '-null-' THEN where_feltetel := where_feltetel || ' AND '; END IF;
    where_feltetel := where_feltetel || 'termekek.ar BETWEEN ' || ar_tol || ' AND ' || ar_ig;
  END IF;
  
  sql_query := sql_query || where_feltetel;
  
  OPEN kurzor FOR sql_query;
  RETURN kurzor;
END KERES;
/
--trigger műfajok törléséhez
CREATE OR REPLACE TRIGGER MUFAJOK_DEL_TRIGGER 
BEFORE DELETE ON MUFAJOK 
FOR EACH ROW 
BEGIN
  DELETE FROM TermekMufaj WHERE mufaj_azonosito=:OLD.mufaj_azonosito;
END;
/

--számla archiváló eljárás
create or replace
procedure szamlaarchival( szamla_id IN szamlak.szamla_azonosito%TYPE )
AS
  db integer;
BEGIN
  select count(*) into db from szamlak where szamla_azonosito=szamla_id;
  if db > 0 then --létezik a számla
    --számla beszúrása szamlaarchiv-ba
    insert into szamlakarchiv (szamla_azonosito,vasarlo,vasarlasi_cim,atveteli_cim,datum)
    select szamlak.szamla_azonosito,felhasznalok.valodi_nev as vasarlo,vasarlashelye.cim as vasarlasi_cim,atvetelhelye.cim as atveteli_cim,szamlak.datum
    from szamlak
    join felhasznalok on szamlak.felhasznaloi_azonosito = felhasznalok.felhasznaloi_azonosito
    join aruhazak vasarlashelye on szamlak.aruhaz_azonosito = vasarlashelye.aruhaz_azonosito
    join aruhazak atvetelhelye on szamlak.atvetel_helye = atvetelhelye.aruhaz_azonosito
    where szamla_azonosito=szamla_id;
    --szamlatetelek fevétele szamlatetelarchiv-ba
    insert into szamlatetelarchiv (szamla_azonosito,termek_neve,db,ar)
    select szamlatetel.szamla_azonosito, termekek.cim as termek_neve, szamlatetel.db, termekek.ar
    from szamlatetel join termekek on szamlatetel.termek_azonosito = termekek.termek_azonosito
    where szamla_azonosito = szamla_id;
    --az eredeti számlákon lévő tételek törölhetőek
    DELETE FROM szamlatetel where szamla_azonosito=szamla_id;
    --és a maguk a számlák is
    DELETE FROM szamlak where szamla_azonosito=szamla_id;
  end if;
END szamlaarchival;
/
--eljárás user törléshez
create or replace
procedure deleteuser( user_id felhasznalok.felhasznaloi_azonosito%TYPE )
AS
  cursor kurzor is select szamla_azonosito from szamlak where felhasznaloi_azonosito = user_id;
  db integer;
BEGIN
  select count(*) into db from felhasznalok where felhasznaloi_azonosito = user_id;
  if db > 0 then --létezik a felhasználó
    --felhasználóhoz kapcsolódó számlák archiválása
    for rekord in kurzor loop
      szamlaarchival(rekord.szamla_azonosito);
    end loop;
    
    delete from felhasznalok where felhasznaloi_azonosito = user_id;
  end if;
END deleteuser;
/
--eljárás áruház törléshez
create or replace
procedure deletearuhaz( shop_id aruhazak.aruhaz_azonosito%TYPE )
AS
  cursor kurzor is select szamla_azonosito from szamlak where aruhaz_azonosito = shop_id;
  db integer;
BEGIN
  select count(*) into db from aruhazak where aruhaz_azonosito = shop_id;
  if db > 0 then --létezik a felhasználó
    --felhasználóhoz kapcsolódó számlák archiválása
    for rekord in kurzor loop
      szamlaarchival(rekord.szamla_azonosito);
    end loop;
    
    delete from nyilvantartas where aruhaz_azonosito = shop_id;
    
    delete from aruhazak where aruhaz_azonosito = shop_id;
  end if;
END deletearuhaz;
/
--eljárás termék törléséhez
create or replace
procedure deletetermek( termek_id termekek.termek_azonosito%TYPE )
AS
  cursor kurzor is select distinct szamla_azonosito from szamlatetel where termek_azonosito = termek_id;
  db integer;
BEGIN
  select count(*) into db from termekek where termek_azonosito = termek_id;
  if db > 0 then --létezik a felhasználó
    --felhasználóhoz kapcsolódó számlák archiválása
    for rekord in kurzor loop
      szamlaarchival(rekord.szamla_azonosito);
    end loop;
    
    delete from termekmufaj where termek_azonosito = termek_id;
    delete from nyilvantartas where termek_azonosito = termek_id;
    
    delete from konyvek where termek_azonosito = termek_id;
    delete from filmek where termek_azonosito = termek_id;
    delete from zenek where termek_azonosito = termek_id;
    delete from ekonyvek where termek_azonosito = termek_id;
    
    delete from termekek where termek_azonosito = termek_id;
  end if;
END deletetermek;
/
--új áruházhoz automatikus készlet
create or replace
trigger kezdokeszlet
after insert on aruhazak
for each row
begin
	FOR rekord IN (SELECT termek_azonosito FROM termekek) LOOP
	  INSERT INTO nyilvantartas (aruhaz_azonosito,termek_azonosito,db) VALUES(:NEW.aruhaz_azonosito,rekord.termek_azonosito,10 );
	END LOOP;
end;
/

----------------------------Tesztadatok feltöltése----------------------------
--megyjegzsés: jelszo: 12345
INSERT INTO Felhasznalok ( felhasznalonev, jelszo, email, valodi_nev, lakcim, telefonszam, jogosultsag ) VALUES ( 'extra', '8cb2237d0679ca88db6464eac60da96345513964', 'extra@gmail.hu', 'extra bácsi', 'Szeged, extra utca 4', '06/90-123-456', 4 );
INSERT INTO Felhasznalok ( felhasznalonev, jelszo, email, valodi_nev, lakcim, telefonszam, jogosultsag ) VALUES ( 'admin', '8cb2237d0679ca88db6464eac60da96345513964', 'admin@gmail.hu', 'admin bácsi', 'Szeged, admin utca 4', '06/90-123-456', 3 );

INSERT INTO ARUHAZAK (CIM,TELEFONSZAM) VALUES ('WebShop','-');
